from flask import Flask, render_template, request, redirect, url_for, flash, jsonify
from flask_mysqldb import MySQL
import config
import pymysql

app = Flask(__name__)
app.config.from_object(config)

# 手动加载数据库配置
app.config['MYSQL_HOST'] = config.DB_CONFIG['host']
app.config['MYSQL_USER'] = config.DB_CONFIG['user']
app.config['MYSQL_PASSWORD'] = config.DB_CONFIG['password']
app.config['MYSQL_DB'] = config.DB_CONFIG['database']

# 初始化 MySQL
mysql = MySQL(app)


# 数据库初始化函数
def init_database():
    try:
        # 连接到 MySQL 服务器（不指定数据库）
        # conn = mysql.connection
        conn = pymysql.connect(
            host=app.config['MYSQL_HOST'],
            user=app.config['MYSQL_USER'],
            password=app.config['MYSQL_PASSWORD'],
            charset='utf8mb4'
        )
        # 2. 创建数据库（如果不存在）
        with conn.cursor() as cursor:
            cursor.execute("CREATE DATABASE IF NOT EXISTS flask_crud")

        # 3. 连接到新创建的数据库
        conn.select_db('flask_crud')

        # 4. 创建 users 表（如果不存在）
        with conn.cursor() as cursor:
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(100) UNIQUE NOT NULL,
                age INT
            )
            ''')
        # cur = conn.cursor()

        # 创建数据库（如果不存在）
        # cur.execute("CREATE DATABASE IF NOT EXISTS flask_crud")
        # cur.execute("USE flask_crud")

        # 创建 users 表（如果不存在）
        # cur.execute('''
        # CREATE TABLE IF NOT EXISTS users (
        #     id INT AUTO_INCREMENT PRIMARY KEY,
        #     name VARCHAR(100) NOT NULL,
        #     email VARCHAR(100) UNIQUE NOT NULL,
        #     age INT
        # )
        #''')

        conn.commit()
        conn.close()
        print("数据库初始化成功")
        return True
    except Exception as e:
        print(f"数据库初始化失败: {e}")
        return False

# 添加 Flask CLI 命令
@app.cli.command("init-db")
def init_db_command():
    """初始化数据库"""
    if init_database():
        print("数据库已成功初始化")
    else:
        print("数据库初始化失败")

# 校验邮箱
def is_email_exists(email, exclude_id=None):
    """
    校验邮箱是否已存在，支持排除当前编辑用户（编辑场景）
    :param email: 要校验的邮箱
    :param exclude_id: 当前编辑用户的ID（编辑时传，添加时传None）
    :return: True（已存在）/ False（不存在）
    """
    cur = mysql.connection.cursor()
    try:
        if exclude_id:
            # 编辑场景：排除自身
            cur.execute(
                "SELECT id FROM users WHERE email = %s AND id != %s",
                (email, exclude_id)
            )
        else:
            # 添加场景：直接查所有
            cur.execute("SELECT id FROM users WHERE email = %s", (email,))
        return cur.fetchone() is not None
    finally:
        cur.close()

@app.route('/check_email')
def check_email():
    email = request.args.get('email')
    exclude_id = request.args.get('exclude_id', type=int)
    exists = is_email_exists(email, exclude_id)
    return jsonify({"exists": exists})

# 首页：显示用户列表 + 添加表单
@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        # 处理添加用户请求
        name = request.form['name']
        email = request.form['email']
        age = request.form['age']
        
        # 调用校验函数，添加场景exclude_id传None
        if is_email_exists(email):
            flash("该邮箱已被注册，请更换邮箱重试", "error")
            return redirect(url_for('index'))
        # 校验通过，执行插入
        cur = mysql.connection.cursor()
        try:
            cur.execute(
                "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)",
                (name, email, age)
            )
            mysql.connection.commit()
            flash("用户添加成功", "success")
        except Exception as e:
            mysql.connection.rollback()
            flash(f"添加失败：{str(e)}", "error")
        finally:
            cur.close()
        return redirect(url_for('index'))
    
    # 查询所有用户
    cur = mysql.connection.cursor()
    cur.execute("SELECT * FROM users")
    users = cur.fetchall()
    cur.close()
    
    return render_template('index.html', users=users)

@app.route('/edit/<int:id>', methods=['GET', 'POST'])
def edit(id):
    cur = mysql.connection.cursor()
    
    if request.method == 'POST':
        name = request.form['name']
        email = request.form['email']
        age = request.form['age']
        
        # 获取当前用户邮箱
        cur.execute("SELECT email FROM users WHERE id=%s", (id,))
        current_email = cur.fetchone()[0]
        
        # 如果邮箱有变化，检查是否已存在（排除当前用户）
        if email != current_email:
            cur.execute("SELECT id FROM users WHERE email=%s AND id != %s", (email, id))
            if cur.fetchone():
                flash("该邮箱已被其他用户使用")
                cur.close()
                return redirect(url_for('edit', id=id))
        
        # 执行更新
        cur.execute("UPDATE users SET name=%s, email=%s, age=%s WHERE id=%s",
                    (name, email, age, id))
        mysql.connection.commit()
        cur.close()
        return redirect(url_for('index'))
    
    # 获取待编辑用户
    cur.execute("SELECT * FROM users WHERE id=%s", (id,))
    user = cur.fetchone()
    cur.close()
    
    if not user:
        flash("用户不存在")
        return redirect(url_for('index'))
    
    user_dict = {
        'id': user[0],
        'name': user[1],
        'email': user[2],
        'age': user[3]
    }
    
    return render_template('edit.html', user=user_dict)

# 删除用户
@app.route('/delete/<int:id>')
def delete(id):
    cur = mysql.connection.cursor()
    cur.execute("DELETE FROM users WHERE id=%s", (id,))
    mysql.connection.commit()
    cur.close()
    return redirect(url_for('index'))

if __name__ == '__main__':
    app.run(host='0.0.0.0', port='5000')
